﻿Imports System.Data.OleDb
'add com "Microsoft Excel 11.0 Object Library"  
Imports Excel=Microsoft.Office.Interop.Excel   
Imports System.Reflection   
Public Class Form1
    Private saleDS As DataSet1
    Dim conn As OleDbConnection

    Dim dt As New DataTable
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        saleDS = New DataSet1
        conn = New OleDbConnection(connStr)
        FillSalesDataset()
        Me.DataGridView1.DataSource = saleDS.Employee
        DataGridView1.EditMode = DataGridViewEditMode.EditOnEnter


        For Each col As DataGridViewTextBoxColumn In Me.DataGridView1.Columns
            Debug.Print(col.HeaderText)
        Next
        Dim cmb As New DataGridViewComboBoxColumn()
        cmb.HeaderText = "Select Data"
        cmb.Name = "cmb"
        cmb.MaxDropDownItems = 4
        cmb.DataSource = dt  'saleDS.Product
        cmb.DisplayMember = "ProductName"
        cmb.ValueMember = "ProductID"
        Me.DataGridView1.Columns.Add(cmb)
        AddHandler DataGridView1.CellClick, handler
    End Sub

    'Public Event CellClick As DataGridViewCellEventHandler 
    Dim handler As DataGridViewCellEventHandler 

    Private Sub dataGridView1_CellClick(ByVal sender As Object, _
                ByVal e As DataGridViewCellEventArgs) _
                Handles DataGridView1.CellClick
        'SendKeys.Send("{Right}")
        'SendKeys.Send("{HOME}")

        '  EditingControl.SelectionLength = 0
        ' EditingControl.SelectionStart = 2
    End Sub
    Private EditingControl As DataGridViewTextBoxEditingControl

    Public Sub FillSalesDataset()
        FillProductTable()
        FillEmployeeTable()
        FillOrderTable()
    End Sub

    Public Sub FillProductTable() 
        Dim command As New OleDbCommand("select * from product", conn)
        Dim adp As New OleDbDataAdapter(command)

        adp.Fill(dt)
        '   adp.Fill(saleDS.Product)
    End Sub

    Public Sub FillEmployeeTable() 
        Dim command As New OleDbCommand("select * from Employee", conn)
        Dim adp As New OleDbDataAdapter(command)
        adp.Fill(saleDS.Employee)
    End Sub
    Public Sub InsertEmployee1()
        'EmployeeID
        'EmployeeName
        'Age
        Dim insertSql As String = "INSERT INTO Employee " & _
        "(EmployeeID, EmployeeName, Age)VALUES" & _
        "(@EmployeeID, @EmployeeName, @Age)"

        
        Dim insertCmd As New OleDbCommand(insertSql, conn)
        insertCmd.Parameters.Add("@EmployeeID", _
            SqlDbType.NVarChar, 10)
        insertCmd.Parameters.Add("@EmployeeName", _
           SqlDbType.NVarChar, 20)
        insertCmd.Parameters.Add("@Age", _
           SqlDbType.Int, 15)
        insertCmd.Parameters("@EmployeeID").Value = "yang2"
        insertCmd.Parameters("@EmployeeName").Value = "yang"
        insertCmd.Parameters("@Age").Value = 10
        conn.Open()
        Dim res As Integer = insertCmd.ExecuteScalar()
        MsgBox("Insert result count = " & res)
        'Dim adp As New OleDbDataAdapter(command)
        'adp.Fill(saleDS.Employee)
    End Sub

    Public Sub InsertEmployee2()
        'EmployeeID
        'EmployeeName
        'Age
        Dim insertSql As String = "INSERT INTO Employee " & _
        "(EmployeeID, EmployeeName, Age)VALUES" & _
        "('" & "yang7" & Now.ToString & "', '" & "yang" & "', 11)"
        ' Dim insertSql As String = "Insert into Qry_Master(Opr_Master.Opr_Name,Opr_Master.Opr_Code,Opr_Master.Opr_Act,Stn_Master.Stn_Name,Stn_Master.Stn_No)Values ('" & Opr_Name.Text & "','" & Opr_Code.Text & "','" & Opr_Act.Text & "','" & Stn_Name.Text & "','" & Stn_No.Text & "')"
        Dim insertCmd As New OleDbCommand(insertSql, conn)
        insertCmd.Parameters.Add("EmployeeID", _
            SqlDbType.NVarChar, 10).Value = "yang6"
        insertCmd.Parameters.Add("EmployeeName", _
           SqlDbType.NVarChar, 20).Value = "yang2"
        insertCmd.Parameters.Add("Age", _
           SqlDbType.Int, 15).Value = 111
        'insertCmd.Parameters("EmployeeID").Value = "yang2"
        'insertCmd.Parameters("EmployeeName").Value = "yang"
        'insertCmd.Parameters("Age").Value = 10
        conn.Open()
        Dim res As String = insertCmd.ExecuteNonQuery()
        MsgBox("Insert result count = " & res) 
        'Dim adp As New OleDbDataAdapter(command)
        'adp.Fill(saleDS.Employee)
    End Sub
    Public Sub FillOrderTable()
        Dim command As New OleDbCommand("select * from [Order]", conn)
        Dim adp As New OleDbDataAdapter(command)
        adp.Fill(saleDS.Order)
    End Sub

    Public Const connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Learning\SVNWorking\datagridviewTest\Sales.mdb"

     
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Dim xlApp As Excel.Application
        'Dim xlWorkBook As Excel.Workbook
        'Dim xlWorkSheet As Excel.Worksheet
        'Dim misValue As Object = System.Reflection.Missing.Value
        'Dim i As Integer
        'Dim j As Integer 

        'xlApp = New Excel.Application 
        'xlWorkBook = xlApp.Workbooks.Add(misValue) 
        'xlWorkSheet = xlWorkBook.Sheets("sheet1") 

        'For Each col As DataGridViewColumn In DataGridView1.Columns
        '    xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
        'Next

        'For i = 2 To DataGridView1.Rows.Count - 1
        '    For j = 0 To DataGridView1.ColumnCount - 1
        '        Dim vv As String
        '        If DataGridView1(j, i).Value Is Nothing Then
        '            vv = "Niet ingevuld"
        '        Else
        '            vv = DataGridView1(j, i).Value.ToString
        '        End If
        '        xlWorkSheet.Cells(i + 1, j + 1) = vv
        '    Next 
        'Next 

        'xlWorkBook.Activate()
        'xlWorkBook.SaveAs("F:\Learning\SVNWorking\datagridviewTest\export.xls") 
        'xlWorkBook.Close()
        'xlApp.Quit()

        'MsgBox("You can find your report at " & "F:\Learning\SVNWorking\datagridviewTest\export.xls")

        Dim total As Decimal = 0
        For Each row As DataGridViewRow In DataGridView1.Rows
            '0 is index of the 1st column, specify your own column index, or even name, like ["columnName"]
            total += Convert.ToDecimal(row.Cells("Quantity").Value)
        Next
        MsgBox(total.ToString())

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        'EditingControl = CType(e.Control, DataGridViewTextBoxEditingControl)
        'EditingControl.
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        InsertEmployee2()
    End Sub
End Class
